﻿
#region NameSpace
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
#endregion


namespace Eb.StockManagement
{
    #region  Struct
    public struct SearchCriteria
    {
        string _setCriteria;

        public string SetCriteria
        {
            get { return _setCriteria; }
            set { _setCriteria = value; }
        }

        string _purchaseOrderID;

        public string PurchaseOrderID
        {
            get { return _purchaseOrderID; }
            set { _purchaseOrderID = value; }
        }

        string _productName;

        public string ProductName
        {
            get { return _productName; }
            set { _productName = value; }
        }

        string _dateofCreation;

        public string DateofCreation
        {
            get { return _dateofCreation; }
            set { _dateofCreation = value; }
        }
        string _dateofCreation1;

        public string DateofCreation1
        {
            get { return _dateofCreation1; }
            set { _dateofCreation1 = value; }
        }
        string _status;
        public string DateStatus
        {
            get { return _status; }
            set { _status = value; }
        }

    }

    public struct LocalSearchCriteria
    {
        string _setCriteria;

        public string SetCriteria
        {
            get { return _setCriteria; }
            set { _setCriteria = value; }
        }

        string _requester;

        public string Requester
        {
            get { return _requester; }
            set { _requester = value; }
        }

        double _approximateAmt;

        public double ApproximateMount
        {
            get { return _approximateAmt; }
            set { _approximateAmt = value; }
        }

        string _amountSelect;

        public string AmountSelectStatus
        {
            get { return _amountSelect; }
            set { _amountSelect = value; }
        }

        string _dateofCreation;

        public string DateofCreation
        {
            get { return _dateofCreation; }
            set { _dateofCreation = value; }
        }
        string _dateofCreation1;

        public string DateofCreation1
        {
            get { return _dateofCreation1; }
            set { _dateofCreation1 = value; }
        }
        string _status;
        public string DateStatus
        {
            get { return _status; }
            set { _status = value; }
        }
    }

    public struct DepartmentSearchCriteria
    {
        string _setCriteria;
        public string SetCriteria
        {
            get { return _setCriteria; }
            set { _setCriteria = value; }
        }

        string _movedBy;

        public string MovedBy
        {
            get { return _movedBy; }
            set { _movedBy = value; }
        }

        string _productCode;

        public string ProductCode
        {
            get { return _productCode; }
            set { _productCode = value; }
        }
        string _productName;

        public string ProductName
        {
            get { return _productName; }
            set { _productName = value; }
        }
        string _dateofCreation;

        public string DateofCreation
        {
            get { return _dateofCreation; }
            set { _dateofCreation = value; }
        }
        string _dateofCreation1;

        public string DateofCreation1
        {
            get { return _dateofCreation1; }
            set { _dateofCreation1 = value; }
        }
        string _status;
        public string DateStatus
        {
            get { return _status; }
            set { _status = value; }
        }
    }
    #endregion

    /// <summary>
    /// Summary description for StockManagement
    /// </summary>
    public class StockManagement
    {
        #region Constructor
        public StockManagement()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        #endregion

        #region Methods

        /// <summary>
        /// Get Materials
        /// </summary>
        /// <returns></returns>
        public DataSet GetBatchHistory()
        {
            #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            DataSet batchHistoryDS;
            #endregion

            batchHistoryDS = new DataSet();
            query = "SELECT     BatchHistory.LotID, dbo.FormatDateTime(BatchHistory.DateTime,'LONGDATEANDTIME') as DateTime,isnull(UserDetails.UD_FirstName,'')+' '+isnull(UserDetails.UD_MiddleName,' ')+' '+isnull(UserDetails.UD_LastName,' ') as EmployeeName , " +
                    " BatchHistory.Direction, BatchHistory.LotName, Direction.DirectionName "+
                    " FROM BatchHistory INNER JOIN UserDetails ON BatchHistory.MovedBy = UserDetails.UD_UserPersonalID INNER JOIN "+
                    " Direction ON BatchHistory.Direction = Direction.DirectionId order by BatchHistory.DateTime desc";
            batchHistoryDS = commonClass.ExecuteQuery(query);
            return batchHistoryDS;
        }

        /// <summary>
        /// Get Product History
        /// </summary>
        /// <param name="lotId"></param>
        /// <returns></returns>
        public DataSet GetProductHistory(int lotId)
        {
             #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            DataSet productHistoryDS;
            #endregion

            productHistoryDS = new DataSet();
            query = "SELECT     ProductList.ProductName, BatchHistory.LotName, BatchHistory.Direction, BatchHistory.DateTime, History.Quantity, BatchHistory.LotID, "+
                    " Direction.DirectionName FROM  BatchHistory INNER JOIN  History ON BatchHistory.LotID = History.LotID INNER JOIN "+
                    " ProductList ON History.ItemID = ProductList.ProductID INNER JOIN  Direction ON BatchHistory.Direction = Direction.DirectionId where BatchHistory.LotID='"+lotId+"'";
            productHistoryDS = commonClass.ExecuteQuery(query);
            return productHistoryDS;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="productId"></param>
        /// <param name="quantity"></param>
        /// <param name="unit"></param>
        /// <returns></returns>
        public bool UpdateProductStock(int productId, int quantity, string unit)
        {
            #region Fields
            string query = string.Empty;
            int recCount = 0;
            bool status;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion
            query = "UPDATE    [ProductStock] SET  Quantity ='" + quantity + "', Unit ='" + unit + "', LastUpdated =getdate() WHERE ProductID='" + productId + "'";
            recCount = commonClass.ExecuteNonQuery(query);
            if (recCount > 0)
                status = true;
            else
                status = false;
            return status;
        }
        public bool UpdateCurrentAvailabilityMaterialStock(int materialID, int quantity, string unit)
        {
             #region Fields
            string query=string.Empty;
            DataSet productIDDS;
            bool status;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            int recCount = 0;
            #endregion
            bool matchnotFound = false;
            productIDDS=new DataSet();
            productIDDS = GetMaterilaIDFromMaterialStock();
            if (productIDDS.Tables[0].Rows.Count == 0)
            {
                query = " INSERT INTO MaterialStock (MaterialID, Quantity, Stock, LastUpdated) VALUES     ('"+materialID+"','"+quantity+"','"+unit+"',getdate())";
                recCount = commonClass.ExecuteNonQuery(query);
                matchnotFound = true;
            }
            else if (productIDDS.Tables[0].Rows.Count > 0)
            {
                for (int count = 0; count < productIDDS.Tables[0].Rows.Count; count++)
                {
                    if (productIDDS.Tables[0].Rows[count][0].ToString() == materialID.ToString())
                    {
                        query = "UPDATE    [MaterialStock] SET  Quantity =Quantity+'" + quantity + "', Stock ='" + unit + "', LastUpdated =getdate() WHERE MaterialID='" + materialID + "'";
                        recCount = commonClass.ExecuteNonQuery(query);
                        matchnotFound = true;
                        //update table
                        break;
                    }
                }
            }
            if (!matchnotFound)
            {
                //insert
                query = "INSERT INTO [MaterialStock] (MaterialID, Quantity, Stock, LastUpdated) VALUES     ('" + materialID + "','" + quantity + "','" + unit + "',getdate())";
                recCount = commonClass.ExecuteNonQuery(query);

            }
            if (recCount > 0)
                status = true;
            else
                status = false;
            return status;
//           
        }
        public bool UpdateProductStock(int productId,int quantity)
        {
            #region Fields
            string query=string.Empty;
            bool status;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            int recCount = 0;
            #endregion  
            query = "UPDATE    [ProductStock] SET  Quantity ='" + quantity + "',LastUpdated =getdate() WHERE ProductID='" + productId + "'";
            recCount = commonClass.ExecuteNonQuery(query);
            if (recCount > 0)
                status = true;
            else
                status = false;
            return status;

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="productId"></param>
        /// <returns>bool</returns>
        public bool UpdateCurrentAvailabilityOfStock(int productId,int quantity,string unit)
        {
            #region Fields
            string query=string.Empty;
            DataSet productIDDS;
            bool status;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            int recCount = 0;
            #endregion
            bool matchnotFound = false;
            productIDDS=new DataSet();
            productIDDS=GetProductIDFromProductStock();
            if (productIDDS.Tables[0].Rows.Count==0)
            {
                 query = "INSERT INTO [ProductStock] (ProductID, Quantity, Unit, LastUpdated) VALUES     ('"+productId+"','"+quantity+"','"+unit+"',getdate())";
                recCount = commonClass.ExecuteNonQuery(query);
                matchnotFound = true;

            }
            else if(productIDDS.Tables[0].Rows.Count>0)
            {
                for (int count=0;count<productIDDS.Tables[0].Rows.Count;count++)
                {
                    if(productIDDS.Tables[0].Rows[count][0].ToString()==productId.ToString())
                    {
                        query = "UPDATE    [ProductStock] SET  Quantity =Quantity+'" + quantity + "', Unit ='" + unit + "', LastUpdated =getdate() WHERE ProductID='" + productId + "'";
                        recCount = commonClass.ExecuteNonQuery(query);
                        matchnotFound = true;
                        //update table
                        break;
                    }
                    //else
                    //{
                    //    //insert
                    //    query = "INSERT INTO [ProductStock] (ProductID, Quantity, Unit, LastUpdated) VALUES     ('"+productId+"','"+quantity+"','"+unit+"',getdate())";
                    //    recCount = commonClass.ExecuteNonQuery(query);
                    //    break;
                    //}
                }
            }
            //else
            //{
            //    //insert
            //    query = "INSERT INTO [ProductStock] (ProductID, Quantity, Unit, LastUpdated) VALUES     ('"+productId+"','"+quantity+"','"+unit+"',getdate())";
            //    recCount = commonClass.ExecuteNonQuery(query);
            //}
            if (!matchnotFound)
            {
                //insert
                query = "INSERT INTO [ProductStock] (ProductID, Quantity, Unit, LastUpdated) VALUES     ('" + productId + "','" + quantity + "','" + unit + "',getdate())";
                recCount = commonClass.ExecuteNonQuery(query);

            }
            if (recCount > 0)
                status = true;
            else
                status = false;
            return status;
        }

        /// <summary>
        /// Get ProductId
        /// </summary>
        /// <returns></returns>
        protected DataSet GetProductIDFromProductStock()
        {
            #region Fields
            DataSet productIDDS;
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            productIDDS = new DataSet();
            query = "SELECT     ProductID FROM         [ProductStock]";
            productIDDS = commonClass.ExecuteQuery(query);
            return productIDDS;
        }
        public DataSet GetMaterilaIDFromMaterialStock()
        {
            #region Fields
            DataSet productIDDS;
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion

            productIDDS = new DataSet();
            query = "SELECT     MaterialID  FROM         MaterialStock";
            productIDDS = commonClass.ExecuteQuery(query);
            return productIDDS;
        }
        /// <summary>
        /// To track the movement of stock
        /// </summary>
        /// <returns></returns>
        protected bool InsertHistory(string itemID,int quantity,int lotID)
        {
            #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            int count = 0;
            bool status;
            #endregion

            query = "INSERT INTO History(ItemID, Quantity, LotID)VALUES     ('"+itemID+"','"+quantity+"','"+lotID+"')";
            count = commonClass.ExecuteNonQuery(query);
            if (count > 0)
                status = true;
            else
                status = false;
            return status;
        }

        public bool InsertBatchHistory(string itemID, int quantity, int direction, string invoiceNo, string ProductionBatchNo, string movedBy,string lotName,string souurce,string destination)
        {
            #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            int count = 0;
            bool status=false;
            int lotID = 0;
            #endregion

            query = "INSERT INTO BatchHistory(Direction, InvoiceNo, ProductionBatchNo, MovedBy, LotName, Source, Destination, DateTime) " +
                   " VALUES     ('"+direction+"','"+invoiceNo+"','"+ProductionBatchNo+"','"+movedBy+"','"+lotName+"','"+souurce+"','"+destination+"',getdate())  Select Scope_Identity()";
            object id = commonClass.ExecuteScalar(query);
            lotID = Convert.ToInt16(id);
            if (lotID != 0)
            {
               string strQuery = "INSERT INTO History(ItemID, Quantity, LotID)VALUES     ('" + itemID + "','" + quantity + "','" + lotID + "')";
               count = commonClass.ExecuteNonQuery(strQuery);
               if (count > 0)
                   status = true;
               else
                   status = false;
            }
            return status;
        }

        public int InsertBatHistory(int direction, string invoiceNo, string ProductionBatchNo, string movedBy, string lotName, string souurce, string destination)
        {
            #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            int count = 0;
            bool status = false;
            int lotID = 0;
            #endregion

            query = "INSERT INTO BatchHistory(Direction, InvoiceNo, ProductionBatchNo, MovedBy, LotName, Source, Destination, DateTime) " +
                   " VALUES     ('" + direction + "','" + invoiceNo + "','" + ProductionBatchNo + "','" + movedBy + "','" + lotName + "','" + souurce + "','" + destination + "',getdate())  Select Scope_Identity()";
            object id = commonClass.ExecuteScalar(query);
            lotID = Convert.ToInt16(id);
            return lotID;
        }
        public bool InsertHistory(int lotID, int produtID, int quantity)
        {
            #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            int count = 0;
            bool status = false;
            #endregion
            string strQuery = "INSERT INTO History(ItemID, Quantity, LotID)VALUES     ('" + produtID + "','" + quantity + "','" + lotID + "')";
            count = commonClass.ExecuteNonQuery(strQuery);
            if (count > 0)
                status = true;
            else
                status = false;
            return status;
        }
        public DataSet GetProdustStockHistory()
        {
             #region Fields
            string query = string.Empty;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion
            query="SELECT     ProductList.ProductID, ProductList.ProductCode, ProductList.ProductName, ProductList.ProductDescription, ProductList.ListPrice, "+
                      "ProductStock.Quantity, dbo.FormatDateTime(ProductStock.LastUpdated,'SHORTDATE') as LastUpdated, ProductType.TypeName, ProductList.Unit, ProductList.Specification " +
                       " FROM         ProductList INNER JOIN  ProductStock ON ProductList.ProductID = ProductStock.ProductID INNER JOIN "+
                      " ProductType ON ProductList.Type = ProductType.Type order by ProductStock.LastUpdated desc";
            DataSet stockStatus = commonClass.ExecuteQuery(query);
            return stockStatus;
        }

        public DataSet GetItemDetailsByCategoryID()
        {
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            DataSet ItemDS = new DataSet();
            string query=string.Empty;
          
                //ProductList
                query = "SELECT     ProductList.ProductID as ItemID, ProductList.ProductName as ItemName, ProductStock.Quantity as Stock" +
                        " FROM  ProductList INNER JOIN  ProductStock ON ProductList.ProductID = ProductStock.ProductID ";
                ItemDS = commonClass.ExecuteQuery(query);

            return ItemDS;
        }

        public bool UpdateItemQty(int category,int Qty,int itemID)
        {
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            int count = 0;
            bool status = false;

            if(category==1)
            {
                query = "UPDATE    ProductStock SET  Quantity =Quantity+'" + Qty + "', LastUpdated =getdate() where ProductID ='" + itemID + "'";
                count = commonClass.ExecuteNonQuery(query);
                    if(count>0)
                        status=true;
                    else
                        status=false;
            }
            else if(category==2)
            {
                query = "UPDATE    MaterialStock SET   Quantity ='" + Qty + "',LastUpdated =getdate() where MatetialID ='" + itemID + "'";
                count = commonClass.ExecuteNonQuery(query);
                if (count > 0)
                    status = true;
                else
                    status = false;
            }
            return status;
        }

        public DataSet GetPurchaseOrderID()
        {
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;

            query = "SELECT     PurchaseOrderID, PurchaseRequestID FROM       PurchaseOrderList " +
                   " where Status=3";
            DataSet OrderID = commonClass.ExecuteQuery(query);
            return OrderID;
        }

        public DataSet GetPurchaseOrderDetails()
        {
           EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query="SELECT     PurchaseOrderList.PurchaseOrderID,CASE WHEN PurchaseOrderList.PurchaseType = 1 THEN 'Production Wise' ELSE 'General' END AS PurchaseType, "+
                  " PurchaseOrderList.PurchaseType AS PurchaseType,convert(varchar,RequestList.CreationTime,103) as DateProcessed, isnull(UserDetails.UD_FirstName,'')+ ' '+ isnull(UserDetails.UD_MiddleName,'')+ "+
                  " isnull(UserDetails.UD_LastName,'') as Requester,RequestList.RequestID FROM         RequestList INNER JOIN  PurchaseOrderList ON RequestList.LinkKey = PurchaseOrderList.PurchaseOrderID INNER JOIN " +
                  " UserDetails ON RequestList.RequesterID = UserDetails.UD_UserPersonalID where RequestList.Status=4 order by DateProcessed";
             DataSet OrderDS = commonClass.ExecuteQuery(query);
            return OrderDS;
        }

        public DataSet GetPurchaseOrderData(int orderID)
        {
            int orderNumber = 0;
              EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "SELECT     PurchaseOrderList.PurchaseOrderID, ProductList.ProductID,PurchaseRequestMaster.PR_RequestID, ProductList.ProductName, " +
                  " PurchaseRequestMaster.PR_ProductQuantity, PurchaseRequestMaster.PR_ApproximateAmount, PurchaseOrderList.PurchaseType, " +
                   " ProductList.ProductCode FROM         PurchaseOrderList INNER JOIN   PurchaseRequestMaster ON PurchaseOrderList.PurchaseRequestID = PurchaseRequestMaster.PR_RequestID INNER JOIN " +
                   " ProductList ON PurchaseRequestMaster.PR_ProductID = ProductList.ProductID where PurchaseOrderList.PurchaseOrderID='"+orderID+"'";

            DataSet OrderDS = commonClass.ExecuteQuery(query);
            return OrderDS;

        }

        public DataSet GetGeneralPurchaseOrderByOrderIS(int orderID)
        {
              EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "SELECT     PurchaseOrderList.PurchaseOrderID, PurchaseRequestMaster.PR_RequestID, PurchaseRequestMaster.PR_ApproximateAmount, " +
                    " PurchaseOrderList.PurchaseType, convert(varchar, PurchaseOrderList.DateOfCreation,103) as DateOfCreation FROM         PurchaseOrderList INNER JOIN " +
                    " PurchaseRequestMaster ON PurchaseOrderList.PurchaseRequestID = PurchaseRequestMaster.PR_RequestID where  PurchaseOrderList.PurchaseOrderID='"+orderID+"'";
            DataSet OrderDS = commonClass.ExecuteQuery(query);
            return OrderDS;
        }
        public DataSet GetMaterialByProductID(int productID)
        {
               EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "SELECT     MaterialList.MaterialID ,MaterialList.MaterialName, MaterialList.Stock, MaterialList.Unit, " +
                    " MaterialList.Price, ProductList.ProductID FROM         ProductMaterialsMap INNER JOIN " +
                    " ProductList ON ProductMaterialsMap.PT_ProductID = ProductList.ProductID INNER JOIN   MaterialList ON ProductMaterialsMap.PT_MaterialID = MaterialList.MaterialID " +
                    " where ProductList.ProductID='"+productID+"'";
            DataSet OrderDS = commonClass.ExecuteQuery(query);
            return OrderDS;

        }

        public bool InsertProcureFromPurchase(int orderId,string date,string note,int PurchaseType)
        {
            int count = 0;
            bool status = false;
               EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "INSERT INTO PurchaseProcure(PurchaseOrderID, DateTime, Note,PurchaseType) " +
                    " VALUES     ('"+orderId+"','"+date+"','"+note+"','"+PurchaseType+"')";
            count = commonClass.ExecuteNonQuery(query);
            if (count > 0)
                status = true;
            else
                status = false;
            return status;

        }
        public DataSet GetGeneralPurchaseOrderMaterialByOrderID(int orderID)
        {
               EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "SELECT     PurchaseRequestGeneral.ProductName, PurchaseRequestGeneral.RequiredQuantity, PurchaseRequestGeneral.UnitPrice, " +
                    " PurchaseRequestGeneral.RequestId FROM         PurchaseOrderList INNER JOIN  PurchaseOrderItemsGeneral ON PurchaseOrderList.PurchaseOrderID = PurchaseOrderItemsGeneral.PurchaseOrderID INNER JOIN " +
                    " PurchaseRequestGeneral ON PurchaseOrderList.PurchaseRequestID = PurchaseRequestGeneral.RequestId where PurchaseOrderList.PurchaseOrderID='"+orderID+"'";
            DataSet orderDS = commonClass.ExecuteQuery(query);
            return orderDS;


        }
        public bool UpdateProductList(int stock,int productID)
        {
             int count = 0;
            bool status = false;
               EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "UPDATE    ProductList SET              Stock =Stock+'" + stock + "' where ProductID='"+productID+"'";
            count = commonClass.ExecuteNonQuery(query);
            if (count > 0)
                status = true;
            else
                status = false;
            return status;
        }
        public DataSet GetProcureHistory()
        {
           EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "SELECT     PurchaseProcure.ProcureID, PurchaseProcure.PurchaseOrderID, CONVERT(varchar, PurchaseProcure.DateTime, 103) AS ReceiveDate, "+
                    " CASE WHEN PurchaseProcure.PurchaseType = 1 THEN 'ProductionWise' ELSE 'General' END AS PurchaseType, PurchaseProcure.Note, "+
                    " case when PurchaseRequestMaster.PR_ProductID>0 then ProductList.ProductName else 'General Purchase' end as ProductName "+
                    " FROM         PurchaseProcure INNER JOIN  PurchaseOrderList ON PurchaseProcure.PurchaseOrderID = PurchaseOrderList.PurchaseOrderID INNER JOIN "+
                    " PurchaseRequestMaster ON PurchaseOrderList.PurchaseRequestID = PurchaseRequestMaster.PR_RequestID LEFT OUTER JOIN "+
                    " ProductList ON PurchaseRequestMaster.PR_ProductID = ProductList.ProductID ";
            DataSet procureDS = commonClass.ExecuteQuery(query);
            return procureDS;
        }

        public DataSet GeneralProcureHistory(int orderID)
        {
             EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "SELECT     PurchaseRequestGeneral.RequestId, PurchaseRequestGeneral.ProductName, PurchaseRequestGeneral.RequiredQuantity, " +
                    " PurchaseRequestGeneral.UnitPrice, PurchaseOrderList.PurchaseOrderID FROM         PurchaseOrderList INNER JOIN PurchaseRequestGeneral ON PurchaseOrderList.PurchaseRequestID = PurchaseRequestGeneral.RequestId INNER JOIN " +
                    " PurchaseProcure ON PurchaseOrderList.PurchaseOrderID = PurchaseProcure.PurchaseOrderID where PurchaseOrderList.PurchaseOrderID='"+orderID+"'";
             DataSet procureDS = commonClass.ExecuteQuery(query);
            return procureDS;
        }
        public DataSet GetProductionWiseProcureHistory(int orderID)
        {
             EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "SELECT     PurchaseOrderList.PurchaseRequestID as RequestId, MaterialList.MaterialName as ProductName, BOMList.Quantity as RequiredQuantity, "+
                    " MaterialList.Price as UnitPrice, PurchaseOrderList.PurchaseOrderID as PurchaseOrderID FROM         PurchaseOrderList INNER JOIN "+
                    " PurchaseProcure ON PurchaseOrderList.PurchaseOrderID = PurchaseProcure.PurchaseOrderID INNER JOIN PurchaseRequestMaster ON PurchaseOrderList.PurchaseRequestID = PurchaseRequestMaster.PR_RequestID INNER JOIN "+
                    " BOMList ON PurchaseRequestMaster.PR_RequestID = BOMList.RequestId INNER JOIN  MaterialList ON BOMList.MaterialID = MaterialList.MaterialID where PurchaseOrderList.PurchaseOrderID='"+orderID+"' ";
            DataSet procureDS = commonClass.ExecuteQuery(query);
            return procureDS;

        }

        public int InsertLocalPurchase(int userID,string remarks,double grdTotalGeneral)
        {
            #region Fields
            string query = string.Empty;
            object identity;
            bool status;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            int purchaseID = 0;
            #endregion
            query = "INSERT INTO LocalPurchaseMaster(CreatedBy, Remarks,ApproximateAmount,CreatedDate) VALUES     ('" + userID + "','" + remarks + "','" + grdTotalGeneral + "',getdate()) Select Scope_Identity()";
            identity = commonClass.ExecuteScalar(query);
            purchaseID = Convert.ToInt32(identity);
            return purchaseID;
        }
        public bool InsertLocalPurchaseItemDetails(int purchaseID,string itemName,string specification,double unitPrice,int qty)
        {
            #region Fields
            string query = string.Empty;
            int recCount = 0;
            bool status;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion
            query = "INSERT INTO LocalPurchase(PurchaseID, ItemName, Specification, UnitPrice, Quantity) " +
                    " VALUES     ('" + purchaseID + "','" + itemName + "','" + specification + "','" + unitPrice + "','" + qty + "')";
            recCount = commonClass.ExecuteNonQuery(query);
            if (recCount > 0)
                status = true;
            else
                status = false;
            return status;
        }

        public bool InsertMaterialHistoryBatchWise(int userId, int materialID, int qty,int uniqID)
        {
            #region Fields
            string query = string.Empty;
            int recCount = 0;
            bool status = false;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion
            string strQuery = "INSERT INTO MaterialHistory(Quantity, MaterialID, LotID)  VALUES     ('" + qty + "','" + materialID + "','" + uniqID + "')";
            recCount = commonClass.ExecuteNonQuery(strQuery);
            if (recCount > 0)
                status = true;
            else
                status = false;
            return status;
        }
        public bool InsertMaterialDetails(int userId,int materialID,int qty)
        {
            #region Fields
            string query = string.Empty;
            int recCount = 0;
            bool status = false;
            int lotID=0;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion
            query = "INSERT INTO MaterialBatchHistory(Direction, InvoiceNo, ProductionBatchNumber, MovedBy, LotName,   DateTime) "+
                    " VALUES     ('" + string.Empty + "','" + string.Empty + "','" + string.Empty + "','" + userId + "','" + string.Empty + "',getdate()) Select Scope_Identity()";
            
             object id = commonClass.ExecuteScalar(query);
            lotID = Convert.ToInt16(id);
            if (lotID != 0)
            {
                string strQuery = "INSERT INTO MaterialHistory(Quantity, MaterialID, LotID)  VALUES     ('"+qty+"','"+materialID+"','"+lotID+"')";
                recCount = commonClass.ExecuteNonQuery(strQuery);
                if (recCount > 0)
                    status = true;
                else
                    status = false;
            }
            return status;
        }

        public bool UpdateMaterialStock(int materialID,int stock)
        {
             #region Fields
            string query = string.Empty;
            int recCount = 0;
            bool status;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion
            query = "UPDATE    MaterialList SET    Stock =Stock-'" + stock + "' where MaterialID ='" + materialID + "'";
            recCount = commonClass.ExecuteNonQuery(query);
            if (recCount > 0)
                status = true;
            else
                status = false;
            return status;
        }

        public int GetRequestTypeIDByLinkKey(int linkKey, int requestType)
        {
             #region Fields
             EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            int requestID = 0;
             #endregion
            query = " SELECT     RequestID FROM         RequestList where RequestTypeID='"+requestType+"' and  LinkKey='"+linkKey+"'";
            object reqId = commonClass.ExecuteScalar(query);
            requestID = Convert.ToInt32(reqId);
            return requestID;
        }

        public bool UpdateRequestListStatus(int requestID,int status)
        {
             #region Fields
            bool status1=true;
             EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
             #endregion
            query = " UPDATE    RequestList SET   Status ='"+status+"' where RequestID='"+requestID+"'";
            int count = commonClass.ExecuteNonQuery(query);
            if (count > 0)
                status1 = true;
            else
                status1 = false;
            return status1;
        }

        public int InsertMaterialBatchHistory(int userId,int ProductId,int direction)
        {
            #region Fields
            int lotID = 0;
            bool status=true;
             EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
             #endregion
            query = " INSERT INTO MaterialBatchHistory(MovedBy, LotName, DateTime, ProductID, Direction) " +
                    " VALUES     ('"+userId+"','"+string.Empty+"',getdate(),'"+ProductId+"','"+direction+"') Select Scope_Identity()";
            object id = commonClass.ExecuteScalar(query);
            lotID = Convert.ToInt16(id);
            return lotID;
        }

        public bool InsertMaterialHistory(int lotID,int materialQty,int materialID)
        {
             #region Fields
            bool status=true;
             EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
             #endregion
            query = "INSERT INTO MaterialHistory(LotID, Quantity, MaterialID) " +
                                " VALUES     ('" + lotID + "','" + materialQty + "','" + materialID + "')";
            int count = commonClass.ExecuteNonQuery(query);
            if (count > 0)
                status = true;
            else
                status = false;
            return status;
        }
        public bool UpdateMaterialStock1(int materialID, int stock)
        {
            #region Fields
            string query = string.Empty;
            int recCount = 0;
            bool status = true;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion
            query = "UPDATE    MaterialList SET    Stock =Stock+'" + stock + "' where MaterialID ='" + materialID + "'";
            recCount = commonClass.ExecuteNonQuery(query);
            if (recCount > 0)
                status = true;
            else
                status = false;
            return status;
        }

        public bool InsertProductSales(int lotID,string date,string workorderID,string customerName,string address,string remarks)
        {
            #region Fields
            string query = string.Empty;
            int recCount = 0;
            bool status = true;
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            #endregion
            query = "INSERT INTO ProductSales(LotID,  Date, WorkOrderID, CustomerName, Address, Remarks) " +
                    " VALUES     ('"+lotID+"','"+date+"','"+workorderID+"','"+customerName+"','"+address+"','"+remarks+"')";
            recCount = commonClass.ExecuteNonQuery(query);
            if (recCount > 0)
                status = true;
            else
                status = false;
            return status;

        }
        public DataSet GetProductDetsils()
        {
             EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query =  "SELECT     ProductID, left(ProductName+SPACE(5),50)+ '|'+'     '+ 'Current Stock:'+ cast(Stock as varchar) as ProductName FROM         ProductList";
             DataSet procureDS = commonClass.ExecuteQuery(query);
            return procureDS;

           
        }

        public DataSet GetProductDtls()
        {
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "SELECT     ProductID, ProductName, ProductCode, ProductDescription, Stock, Type, ListPrice, Unit"+
                    " FROM         ProductList";
            DataSet procureDS = commonClass.ExecuteQuery(query);
            return procureDS;


        }
        public DataSet GetProductDetailsByProductID(int productID)
        {
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "SELECT     ProductID, ProductName, ProductCode, ProductDescription, Stock, Type, ListPrice, Unit "+
                    " FROM         ProductList where ProductID='"+productID+"'";
            DataSet procureDS = commonClass.ExecuteQuery(query);
            return procureDS;
        }
        public DataSet Department()
        {
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "SELECT     DepartmentID, DepartmentName FROM         Department";
            DataSet procureDS = commonClass.ExecuteQuery(query);
            return procureDS;
        }
        public bool SearchDepartmentMoveProcure(DepartmentSearchCriteria criteria, out DataSet result)
        {
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            SqlDataAdapter sqlDataAdapter = commonClass.SQLDataAdapter();
            result = new DataSet();

            if (criteria.ProductCode != null && criteria.ProductCode != "")
            {
                 sqlDataAdapter.SelectCommand.CommandText = "SELECT     ProductList.ProductCode, ProductList.ProductName, History.Quantity,"+
                                                            " BatchHistory.LotID,isnull(UserDetails.UD_FirstName,'')+' '+isnull(UserDetails.UD_MiddleName, '')+' '+isnull(UserDetails.UD_LastName,'') as MovedBy, BatchHistory.ProductionBatchNo, "+
                                                            " convert(varchar,BatchHistory.DateTime,103) as Date FROM         BatchHistory INNER JOIN   History ON BatchHistory.LotID = History.LotID INNER JOIN "+
                                                            " ProductList ON History.ItemID = ProductList.ProductID INNER JOIN   UserDetails ON BatchHistory.MovedBy = UserDetails.UD_UserPersonalID "+
                                                            " where BatchHistory.Direction=1 and   ProductList.ProductCode " + "like '" + '%' + "' + '" + criteria.ProductCode + "' + '" + '%' + "'";


            }
            else if (criteria.ProductName != null && criteria.ProductName != "")
            {
                sqlDataAdapter.SelectCommand.CommandText = "SELECT     ProductList.ProductCode, ProductList.ProductName, History.Quantity," +
                                           " BatchHistory.LotID,isnull(UserDetails.UD_FirstName,'')+' '+isnull(UserDetails.UD_MiddleName, '')+' '+isnull(UserDetails.UD_LastName,'') as MovedBy, BatchHistory.ProductionBatchNo, " +
                                           " convert(varchar,BatchHistory.DateTime,103) as Date FROM         BatchHistory INNER JOIN   History ON BatchHistory.LotID = History.LotID INNER JOIN " +
                                           " ProductList ON History.ItemID = ProductList.ProductID INNER JOIN   UserDetails ON BatchHistory.MovedBy = UserDetails.UD_UserPersonalID " +
                                           " where BatchHistory.Direction=1 and   ProductList.ProductName " + "like '" + '%' + "' + '" + criteria.ProductName + "' + '" + '%' + "'";


            }
            else if (criteria.MovedBy != null && criteria.MovedBy != "")
            {
                sqlDataAdapter.SelectCommand.CommandText = "SELECT     ProductList.ProductCode, ProductList.ProductName, History.Quantity," +
                           " BatchHistory.LotID,isnull(UserDetails.UD_FirstName,'')+' '+isnull(UserDetails.UD_MiddleName, '')+' '+isnull(UserDetails.UD_LastName,'') as MovedBy, BatchHistory.ProductionBatchNo, " +
                           " convert(varchar,BatchHistory.DateTime,103) as Date FROM         BatchHistory INNER JOIN   History ON BatchHistory.LotID = History.LotID INNER JOIN " +
                           " ProductList ON History.ItemID = ProductList.ProductID INNER JOIN   UserDetails ON BatchHistory.MovedBy = UserDetails.UD_UserPersonalID " +
                           " where BatchHistory.Direction=1 and   UserDetails.UD_FirstName " + "like '" + '%' + "' + '" + criteria.MovedBy + "' + '" + '%' + "'";


            }
            else if (criteria.DateofCreation != null && criteria.DateofCreation != "")
            {
                if (criteria.DateStatus == "Between")
                {
                    sqlDataAdapter.SelectCommand.CommandText = "SELECT     ProductList.ProductCode, ProductList.ProductName, History.Quantity, BatchHistory.LotID," +
                           " isnull(UserDetails.UD_FirstName,'')+' '+isnull(UserDetails.UD_MiddleName, '')+' '+isnull(UserDetails.UD_LastName,'') as MovedBy, BatchHistory.ProductionBatchNo, " +
                           " convert(varchar,BatchHistory.DateTime,103) as Date FROM         BatchHistory INNER JOIN  History ON BatchHistory.LotID = History.LotID INNER JOIN " +
                           " ProductList ON History.ItemID = ProductList.ProductID INNER JOIN  UserDetails ON BatchHistory.MovedBy = UserDetails.UD_UserPersonalID " +
                           " where BatchHistory.Direction=1 and   convert(varchar,BatchHistory.DateTime,103) between  convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103) and convert(varchar,cast('" + criteria.DateofCreation1 + "' as datetime),103)";


                }
                else if (criteria.DateStatus == ">=")
                {
                    sqlDataAdapter.SelectCommand.CommandText = "SELECT     ProductList.ProductCode, ProductList.ProductName, History.Quantity, " +
                        " BatchHistory.LotID,isnull(UserDetails.UD_FirstName,'')+' '+isnull(UserDetails.UD_MiddleName, '')+' '+isnull(UserDetails.UD_LastName,'') as MovedBy, BatchHistory.ProductionBatchNo, " +
                        " convert(varchar,BatchHistory.DateTime,103) as Date FROM         BatchHistory INNER JOIN  History ON BatchHistory.LotID = History.LotID INNER JOIN " +
                        " ProductList ON History.ItemID = ProductList.ProductID INNER JOIN  UserDetails ON BatchHistory.MovedBy = UserDetails.UD_UserPersonalID " +
                        "where BatchHistory.Direction=1 and   convert(varchar,BatchHistory.DateTime,103)>= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103)";
                }
                else if (criteria.DateStatus == "<=")
                {
                    sqlDataAdapter.SelectCommand.CommandText = "SELECT     ProductList.ProductCode, ProductList.ProductName, History.Quantity, " +
                        " BatchHistory.LotID,isnull(UserDetails.UD_FirstName,'')+' '+isnull(UserDetails.UD_MiddleName, '')+' '+isnull(UserDetails.UD_LastName,'') as MovedBy, BatchHistory.ProductionBatchNo, " +
                        " convert(varchar,BatchHistory.DateTime,103) as Date FROM         BatchHistory INNER JOIN  History ON BatchHistory.LotID = History.LotID INNER JOIN " +
                        " ProductList ON History.ItemID = ProductList.ProductID INNER JOIN  UserDetails ON BatchHistory.MovedBy = UserDetails.UD_UserPersonalID " +
                        "where BatchHistory.Direction=1 and   convert(varchar,BatchHistory.DateTime,103)<= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103)";
                }
                else if (criteria.DateStatus == "=")
                {
                    sqlDataAdapter.SelectCommand.CommandText = "SELECT     ProductList.ProductCode, ProductList.ProductName, History.Quantity, " +
                        " BatchHistory.LotID,isnull(UserDetails.UD_FirstName,'')+' '+isnull(UserDetails.UD_MiddleName, '')+' '+isnull(UserDetails.UD_LastName,'') as MovedBy, BatchHistory.ProductionBatchNo, " +
                        " convert(varchar,BatchHistory.DateTime,103) as Date FROM         BatchHistory INNER JOIN  History ON BatchHistory.LotID = History.LotID INNER JOIN " +
                        " ProductList ON History.ItemID = ProductList.ProductID INNER JOIN  UserDetails ON BatchHistory.MovedBy = UserDetails.UD_UserPersonalID " +
                        "where BatchHistory.Direction=1 and   convert(varchar,BatchHistory.DateTime,103)= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103)";
                }
            }
            else
            {
                sqlDataAdapter.SelectCommand.CommandText = "SELECT     ProductList.ProductCode, ProductList.ProductName, History.Quantity, BatchHistory.LotID," +
                    " isnull(UserDetails.UD_FirstName,'')+' '+isnull(UserDetails.UD_MiddleName, '')+' '+isnull(UserDetails.UD_LastName,'') as MovedBy, BatchHistory.ProductionBatchNo,  " +
                    " convert(varchar,BatchHistory.DateTime,103) as Date FROM         BatchHistory INNER JOIN   History ON BatchHistory.LotID = History.LotID INNER JOIN " +
                    " ProductList ON History.ItemID = ProductList.ProductID INNER JOIN  UserDetails ON BatchHistory.MovedBy = UserDetails.UD_UserPersonalID " +
                    " where BatchHistory.Direction=1 ";
            }
            sqlDataAdapter.Fill(result);
            return true;
        }
        public bool SearchLocalPurchaseProcure(LocalSearchCriteria criteria,out DataSet result)
        {
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            SqlDataAdapter sqlDataAdapter = commonClass.SQLDataAdapter();
            result = new DataSet();

            if (criteria.Requester != null && criteria.Requester != "")
            {
                sqlDataAdapter.SelectCommand.CommandText = "SELECT     LocalPurchaseMaster.PurchaseID,  LocalPurchaseMaster.Remarks, LocalPurchaseMaster.ApproximateAmount, convert(varchar,LocalPurchaseMaster.CreatedDate,103) as CreatedDate,  " +
                     " isnull(UserDetails.UD_FirstName,' ')+' '+isnull(UserDetails.UD_MiddleName,'')+' '+isnull(UserDetails.UD_LastName,'') as Requester FROM         LocalPurchaseMaster INNER JOIN "+
                     " UserDetails ON LocalPurchaseMaster.CreatedBy = UserDetails.UD_UserPersonalID "+
                     " where UserDetails.UD_FirstName " + "like '" + '%' + "' + '" + criteria.Requester + "' + '" + '%' + "' order by LocalPurchaseMaster.CreatedDate desc";
            }
            else if (criteria.ApproximateMount != 0 )
            {
                if (criteria.AmountSelectStatus == ">=")
                {
                    sqlDataAdapter.SelectCommand.CommandText = "SELECT     LocalPurchaseMaster.PurchaseID,  LocalPurchaseMaster.Remarks, LocalPurchaseMaster.ApproximateAmount, convert(varchar,LocalPurchaseMaster.CreatedDate,103) as CreatedDate,  " +
                     " isnull(UserDetails.UD_FirstName,' ')+' '+isnull(UserDetails.UD_MiddleName,'')+' '+isnull(UserDetails.UD_LastName,'') as Requester FROM         LocalPurchaseMaster INNER JOIN " +
                     " UserDetails ON LocalPurchaseMaster.CreatedBy = UserDetails.UD_UserPersonalID  where  LocalPurchaseMaster.ApproximateAmount >= '" + criteria.ApproximateMount + "'  order by LocalPurchaseMaster.CreatedDate desc";
                }
                else if(criteria.AmountSelectStatus=="<=")
                {
                    sqlDataAdapter.SelectCommand.CommandText = "SELECT     LocalPurchaseMaster.PurchaseID,  LocalPurchaseMaster.Remarks, LocalPurchaseMaster.ApproximateAmount, convert(varchar,LocalPurchaseMaster.CreatedDate,103) as CreatedDate,  " +
                     " isnull(UserDetails.UD_FirstName,' ')+' '+isnull(UserDetails.UD_MiddleName,'')+' '+isnull(UserDetails.UD_LastName,'') as Requester FROM         LocalPurchaseMaster INNER JOIN " +
                     " UserDetails ON LocalPurchaseMaster.CreatedBy = UserDetails.UD_UserPersonalID  where  LocalPurchaseMaster.ApproximateAmount <= '" + criteria.ApproximateMount + "' order by LocalPurchaseMaster.CreatedDate desc";

                }
                else if (criteria.AmountSelectStatus == "=")
                {
                     sqlDataAdapter.SelectCommand.CommandText = "SELECT     LocalPurchaseMaster.PurchaseID,  LocalPurchaseMaster.Remarks, LocalPurchaseMaster.ApproximateAmount, convert(varchar,LocalPurchaseMaster.CreatedDate,103) as CreatedDate,  " +
                     " isnull(UserDetails.UD_FirstName,' ')+' '+isnull(UserDetails.UD_MiddleName,'')+' '+isnull(UserDetails.UD_LastName,'') as Requester FROM         LocalPurchaseMaster INNER JOIN " +
                     " UserDetails ON LocalPurchaseMaster.CreatedBy = UserDetails.UD_UserPersonalID  where  LocalPurchaseMaster.ApproximateAmount = '" + criteria.ApproximateMount + "' order by LocalPurchaseMaster.CreatedDate desc";

                }
            }
            else if (criteria.DateofCreation != null && criteria.DateofCreation != "")
            {
                if (criteria.DateStatus == "Between")
                {
                    
                 sqlDataAdapter.SelectCommand.CommandText = "SELECT     LocalPurchaseMaster.PurchaseID,  LocalPurchaseMaster.Remarks, LocalPurchaseMaster.ApproximateAmount, convert(varchar,LocalPurchaseMaster.CreatedDate,103) as CreatedDate,  "+
                     " isnull(UserDetails.UD_FirstName,' ')+' '+isnull(UserDetails.UD_MiddleName,'')+' '+isnull(UserDetails.UD_LastName,'') as Requester FROM         LocalPurchaseMaster INNER JOIN "+
                    " UserDetails ON LocalPurchaseMaster.CreatedBy = UserDetails.UD_UserPersonalID where convert(varchar,LocalPurchaseMaster.CreatedDate,103) between  convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103) and convert(varchar,cast('" + criteria.DateofCreation1 + "' as datetime),103) order by LocalPurchaseMaster.CreatedDate desc";

                }
                else if (criteria.DateStatus == ">=")
                {
                    sqlDataAdapter.SelectCommand.CommandText = "SELECT     LocalPurchaseMaster.PurchaseID,  LocalPurchaseMaster.Remarks, LocalPurchaseMaster.ApproximateAmount, convert(varchar,LocalPurchaseMaster.CreatedDate,103) as CreatedDate,  " +
                     " isnull(UserDetails.UD_FirstName,' ')+' '+isnull(UserDetails.UD_MiddleName,'')+' '+isnull(UserDetails.UD_LastName,'') as Requester FROM         LocalPurchaseMaster INNER JOIN " +
                     " UserDetails ON LocalPurchaseMaster.CreatedBy = UserDetails.UD_UserPersonalID where convert(varchar,LocalPurchaseMaster.CreatedDate,103)>= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103) order by LocalPurchaseMaster.CreatedDate desc";


                }
                else if (criteria.DateStatus == "<=")
                {
                    sqlDataAdapter.SelectCommand.CommandText ="SELECT     LocalPurchaseMaster.PurchaseID,  LocalPurchaseMaster.Remarks, LocalPurchaseMaster.ApproximateAmount, convert(varchar,LocalPurchaseMaster.CreatedDate,103) as CreatedDate,  "+
                     " isnull(UserDetails.UD_FirstName,' ')+' '+isnull(UserDetails.UD_MiddleName,'')+' '+isnull(UserDetails.UD_LastName,'') as Requester FROM         LocalPurchaseMaster INNER JOIN "+
                     " UserDetails ON LocalPurchaseMaster.CreatedBy = UserDetails.UD_UserPersonalID where convert(varchar,LocalPurchaseMaster.CreatedDate,103)<= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103) order by LocalPurchaseMaster.CreatedDate desc";

                }
                else if (criteria.DateStatus == "=")
                {
                    sqlDataAdapter.SelectCommand.CommandText = "SELECT     LocalPurchaseMaster.PurchaseID,  LocalPurchaseMaster.Remarks, LocalPurchaseMaster.ApproximateAmount, convert(varchar,LocalPurchaseMaster.CreatedDate,103) as CreatedDate,  " +
                     " isnull(UserDetails.UD_FirstName,' ')+' '+isnull(UserDetails.UD_MiddleName,'')+' '+isnull(UserDetails.UD_LastName,'') as Requester FROM         LocalPurchaseMaster INNER JOIN " +
                     " UserDetails ON LocalPurchaseMaster.CreatedBy = UserDetails.UD_UserPersonalID where convert(varchar,LocalPurchaseMaster.CreatedDate,103)= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103) order by LocalPurchaseMaster.CreatedDate desc";
                }
            }
            else
            {
                sqlDataAdapter.SelectCommand.CommandText = "SELECT     LocalPurchaseMaster.PurchaseID,  LocalPurchaseMaster.Remarks, LocalPurchaseMaster.ApproximateAmount, convert(varchar,LocalPurchaseMaster.CreatedDate,103) as CreatedDate,  " +
                    " isnull(UserDetails.UD_FirstName,' ')+' '+isnull(UserDetails.UD_MiddleName,'')+' '+isnull(UserDetails.UD_LastName,'') as Requester FROM         LocalPurchaseMaster INNER JOIN " +
                    " UserDetails ON LocalPurchaseMaster.CreatedBy = UserDetails.UD_UserPersonalID order by LocalPurchaseMaster.CreatedDate desc";
            }
            sqlDataAdapter.Fill(result);
            return true;
        }
        public bool SearchProcureList(SearchCriteria criteria, out DataSet result)
        {
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            SqlDataAdapter sqlDataAdapter = commonClass.SQLDataAdapter();
            result = new DataSet();

            if (criteria.ProductName != null && criteria.ProductName != "")
            {
                sqlDataAdapter.SelectCommand.CommandText = "SELECT     PurchaseProcure.ProcureID, PurchaseProcure.PurchaseOrderID, CONVERT(varchar, PurchaseProcure.DateTime, 103) AS ReceiveDate, "+
                                                           " CASE WHEN PurchaseProcure.PurchaseType = 1 THEN 'ProductionWise' ELSE 'General' END AS PurchaseType, PurchaseProcure.Note, "+
                     " case when PurchaseRequestMaster.PR_ProductID>0 then ProductList.ProductName else 'General Purchase' end as ProductName "+
                     " FROM         PurchaseProcure INNER JOIN  PurchaseOrderList ON PurchaseProcure.PurchaseOrderID = PurchaseOrderList.PurchaseOrderID INNER JOIN  "+
                     " PurchaseRequestMaster ON PurchaseOrderList.PurchaseRequestID = PurchaseRequestMaster.PR_RequestID LEFT OUTER JOIN "+
                     " ProductList ON PurchaseRequestMaster.PR_ProductID = ProductList.ProductID where ProductList.ProductName " + "like '" + '%' + "' + '" + criteria.ProductName + "' + '" + '%' + "'";

            }
            else if (criteria.PurchaseOrderID != null && criteria.PurchaseOrderID != "")
            {
                 sqlDataAdapter.SelectCommand.CommandText = "SELECT     PurchaseProcure.ProcureID, PurchaseProcure.PurchaseOrderID, CONVERT(varchar, PurchaseProcure.DateTime, 103) AS ReceiveDate, "+
                     " CASE WHEN PurchaseProcure.PurchaseType = 1 THEN 'ProductionWise' ELSE 'General' END AS PurchaseType, PurchaseProcure.Note, "+
                     " case when PurchaseRequestMaster.PR_ProductID>0 then ProductList.ProductName else 'General Purchase' end as ProductName  "+
                     " FROM         PurchaseProcure INNER JOIN  PurchaseOrderList ON PurchaseProcure.PurchaseOrderID = PurchaseOrderList.PurchaseOrderID INNER JOIN "+
                     " PurchaseRequestMaster ON PurchaseOrderList.PurchaseRequestID = PurchaseRequestMaster.PR_RequestID LEFT OUTER JOIN "+
                     " ProductList ON PurchaseRequestMaster.PR_ProductID = ProductList.ProductID where PurchaseProcure.PurchaseOrderID " + "like '" + '%' + "' + '" + criteria.PurchaseOrderID + "' + '" + '%' + "'";

            }
            else if (criteria.DateofCreation != null && criteria.DateofCreation != "")
            {
                if (criteria.DateStatus == "Between")
                {
                    sqlDataAdapter.SelectCommand.CommandText = "SELECT     PurchaseProcure.ProcureID, PurchaseProcure.PurchaseOrderID, CONVERT(varchar, PurchaseProcure.DateTime, 103) AS ReceiveDate, " +
                     " CASE WHEN PurchaseProcure.PurchaseType = 1 THEN 'ProductionWise' ELSE 'General' END AS PurchaseType, PurchaseProcure.Note, " +
                     " case when PurchaseRequestMaster.PR_ProductID>0 then ProductList.ProductName else 'General Purchase' end as ProductName  " +
                     " FROM         PurchaseProcure INNER JOIN  PurchaseOrderList ON PurchaseProcure.PurchaseOrderID = PurchaseOrderList.PurchaseOrderID INNER JOIN " +
                     " PurchaseRequestMaster ON PurchaseOrderList.PurchaseRequestID = PurchaseRequestMaster.PR_RequestID LEFT OUTER JOIN  " +
                     " ProductList ON PurchaseRequestMaster.PR_ProductID = ProductList.ProductID where convert(varchar,PurchaseProcure.DateTime,103) between  convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103) and convert(varchar,cast('" + criteria.DateofCreation1 + "' as datetime),103)";

                }
                else if (criteria.DateStatus == "<=")
                {
                    sqlDataAdapter.SelectCommand.CommandText = "SELECT     PurchaseProcure.ProcureID, PurchaseProcure.PurchaseOrderID, CONVERT(varchar, PurchaseProcure.DateTime, 103) AS ReceiveDate, " +
                    " CASE WHEN PurchaseProcure.PurchaseType = 1 THEN 'ProductionWise' ELSE 'General' END AS PurchaseType, PurchaseProcure.Note, " +
                    " case when PurchaseRequestMaster.PR_ProductID>0 then ProductList.ProductName else 'General Purchase' end as ProductName  " +
                    " FROM         PurchaseProcure INNER JOIN  PurchaseOrderList ON PurchaseProcure.PurchaseOrderID = PurchaseOrderList.PurchaseOrderID INNER JOIN " +
                    " PurchaseRequestMaster ON PurchaseOrderList.PurchaseRequestID = PurchaseRequestMaster.PR_RequestID LEFT OUTER JOIN " +
                    " ProductList ON PurchaseRequestMaster.PR_ProductID = ProductList.ProductID where convert(varchar,PurchaseProcure.DateTime,103)<= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103)";
                }
                else if (criteria.DateStatus == ">=")
                {
                    sqlDataAdapter.SelectCommand.CommandText = "SELECT     PurchaseProcure.ProcureID, PurchaseProcure.PurchaseOrderID, CONVERT(varchar, PurchaseProcure.DateTime, 103) AS ReceiveDate, " +
                    " CASE WHEN PurchaseProcure.PurchaseType = 1 THEN 'ProductionWise' ELSE 'General' END AS PurchaseType, PurchaseProcure.Note, " +
                    " case when PurchaseRequestMaster.PR_ProductID>0 then ProductList.ProductName else 'General Purchase' end as ProductName  " +
                    " FROM         PurchaseProcure INNER JOIN  PurchaseOrderList ON PurchaseProcure.PurchaseOrderID = PurchaseOrderList.PurchaseOrderID INNER JOIN " +
                    " PurchaseRequestMaster ON PurchaseOrderList.PurchaseRequestID = PurchaseRequestMaster.PR_RequestID LEFT OUTER JOIN " +
                    " ProductList ON PurchaseRequestMaster.PR_ProductID = ProductList.ProductID where convert(varchar,PurchaseProcure.DateTime,103)>= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103)";
                }
                else
                {
                    sqlDataAdapter.SelectCommand.CommandText = "SELECT     PurchaseProcure.ProcureID, PurchaseProcure.PurchaseOrderID, CONVERT(varchar, PurchaseProcure.DateTime, 103) AS ReceiveDate, " +
                    " CASE WHEN PurchaseProcure.PurchaseType = 1 THEN 'ProductionWise' ELSE 'General' END AS PurchaseType, PurchaseProcure.Note, " +
                    " case when PurchaseRequestMaster.PR_ProductID>0 then ProductList.ProductName else 'General Purchase' end as ProductName  " +
                    " FROM         PurchaseProcure INNER JOIN  PurchaseOrderList ON PurchaseProcure.PurchaseOrderID = PurchaseOrderList.PurchaseOrderID INNER JOIN " +
                    " PurchaseRequestMaster ON PurchaseOrderList.PurchaseRequestID = PurchaseRequestMaster.PR_RequestID LEFT OUTER JOIN " +
                    " ProductList ON PurchaseRequestMaster.PR_ProductID = ProductList.ProductID where convert(varchar,PurchaseProcure.DateTime,103)= convert(varchar,cast('" + criteria.DateofCreation + "' as datetime),103)";

                }

            }
            else
            {
                  sqlDataAdapter.SelectCommand.CommandText =  "SELECT     PurchaseProcure.ProcureID, PurchaseProcure.PurchaseOrderID, CONVERT(varchar, PurchaseProcure.DateTime, 103) AS ReceiveDate, " +
        " CASE WHEN PurchaseProcure.PurchaseType = 1 THEN 'ProductionWise' ELSE 'General' END AS PurchaseType, PurchaseProcure.Note, " +
        " case when PurchaseRequestMaster.PR_ProductID>0 then ProductList.ProductName else 'General Purchase' end as ProductName " +
        " FROM         PurchaseProcure INNER JOIN  PurchaseOrderList ON PurchaseProcure.PurchaseOrderID = PurchaseOrderList.PurchaseOrderID INNER JOIN " +
        " PurchaseRequestMaster ON PurchaseOrderList.PurchaseRequestID = PurchaseRequestMaster.PR_RequestID LEFT OUTER JOIN " +
        " ProductList ON PurchaseRequestMaster.PR_ProductID = ProductList.ProductID ";

            }
            sqlDataAdapter.Fill(result);
            return true;
        }
     
        public DataSet GetLocalPurchaseDetailsByPurchaseID(int purchaseID)
        {
             EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "SELECT     LocalPurchase.PurchaseID,LocalPurchase.ItemName, LocalPurchase.UnitPrice, LocalPurchase.Quantity, LocalPurchase.Specification " +
                    " FROM  LocalPurchaseMaster INNER JOIN   LocalPurchase ON LocalPurchaseMaster.PurchaseID = LocalPurchase.PurchaseID " +
                    " where  LocalPurchase.PurchaseID='"+purchaseID+"'";
            DataSet procureDS = commonClass.ExecuteQuery(query);
            return procureDS;
        }

        public DataSet GetProcureFromDepartmentMove()
        {
              EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "SELECT     ProductList.ProductCode, ProductList.ProductName, History.Quantity, BatchHistory.LotID," +
                    " isnull(UserDetails.UD_FirstName,'')+' '+isnull(UserDetails.UD_MiddleName, '')+' '+isnull(UserDetails.UD_LastName,'') as MovedBy, BatchHistory.ProductionBatchNo,  " +
                    " convert(varchar,BatchHistory.DateTime,103) as Date FROM         BatchHistory INNER JOIN   History ON BatchHistory.LotID = History.LotID INNER JOIN " +
                    " ProductList ON History.ItemID = ProductList.ProductID INNER JOIN  UserDetails ON BatchHistory.MovedBy = UserDetails.UD_UserPersonalID " +
                    " where BatchHistory.Direction=1 ";
            DataSet procureDS = commonClass.ExecuteQuery(query);
            return procureDS;

        }
        public DataSet GetMaterialBatchHistory()
        {
             EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "SELECT     MaterialBatchHistory.LotID, ProductList.ProductCode, ProductList.ProductName, dbo.FormatDateTime(MaterialBatchHistory.DateTime,'SHORTDATE') as DateTime, " +
                    " isnull(UserDetails.UD_FirstName,'')+' '+isnull(UserDetails.UD_MiddleName,'')+' '+isnull(UserDetails.UD_LastName,'') as MovedBy FROM         MaterialBatchHistory INNER JOIN " +
                    " ProductList ON MaterialBatchHistory.ProductID = ProductList.ProductID INNER JOIN  UserDetails ON MaterialBatchHistory.MovedBy = UserDetails.UD_UserPersonalID order by MaterialBatchHistory.DateTime desc";
            DataSet procureDS = commonClass.ExecuteQuery(query);
            return procureDS;
        }
        public DataSet GetMaterialHistoryByLotID(int lotID)
        {
             EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "SELECT     MaterialHistory.LotID, MaterialList.MaterialName, MaterialHistory.Quantity, MaterialList.Price " +
                    " FROM   MaterialHistory INNER JOIN   MaterialList ON MaterialHistory.MaterialID = MaterialList.MaterialID " +
                    " where MaterialHistory.LotID='"+lotID+"'";
            DataSet procureDS = commonClass.ExecuteQuery(query);
            return procureDS;
        }
        public DataSet IssueFromSales()
        {
             EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "SELECT     BatchHistory.LotID, ProductSales.WorkOrderID,dbo.FormatDateTime(ProductSales.Date,'SHORTDATE') as Date, ProductSales.CustomerName, ProductSales.Address,  " +
                    " isnull(UserDetails.UD_FirstName, '')+' '+isnull(UserDetails.UD_MiddleName,'')+' '+isnull(UserDetails.UD_LastName,'') as MovedBy FROM         BatchHistory INNER JOIN" +
                    " ProductSales ON BatchHistory.LotID = ProductSales.LotID INNER JOIN  UserDetails ON BatchHistory.MovedBy = UserDetails.UD_UserPersonalID order by ProductSales.Date desc";
            DataSet procureDS = commonClass.ExecuteQuery(query);
            return procureDS;
        }
        public DataSet IssueFromSalesByLotID(int lotID)
        {
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "SELECT     History.LotID,ProductList.ProductCode, ProductList.ProductName, History.Quantity FROM         History INNER JOIN " +
                    " ProductList ON History.ItemID = ProductList.ProductID where History.LotID='"+lotID+"'";
            DataSet procureDS = commonClass.ExecuteQuery(query);
            return procureDS;
        }

        public DataSet GetDirection()
        {
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = " SELECT     DirectionId, DirectionName FROM         Direction";
            DataSet procureDS = commonClass.ExecuteQuery(query);
            return procureDS;
        }

        public int InsertIssueForOthers(int movedBy,int movedTo,string fromDate,string toDate,int direction,string remarks)
        {
            int uniqId = 0;
             EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "INSERT INTO IssueForOthers(Date, MovedBy, MovedTo, FromDate, ToDate, Direction, Remarks) " +
                " VALUES     (getdate(),'" + movedBy + "','" + movedTo + "','" + fromDate + "','" + toDate + "','" + direction + "','" + remarks + "') Select Scope_Identity()";
            object id = commonClass.ExecuteScalar(query);
            uniqId = Convert.ToInt16(id);
            return uniqId;
        }

        public bool InsertIssueForOthers(int id, string itemName, int quantity,string specification)
        {
            bool status;
             int uniqId = 0;
             EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "INSERT INTO IssueOthers (Id, ItemName, Quantity,Specification) VALUES   " +
                "('"+id+"','"+itemName+"','"+quantity+"','"+specification+"')";
            int recCount=  commonClass.ExecuteNonQuery(query);
            if (recCount > 0)
                status = true;
            else
                status = false;
            return status;
        }
        public DataSet GetIssueForOthers()
        {
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "SELECT     IssueForOthers.Id, isnull(UserDetails_1.UD_FirstName,'')+' '+isnull(UserDetails_1.UD_MiddleName,'')+' '+isnull(UserDetails_1.UD_LastName,' ') as MovedBy, " +
                    " isnull(UserDetails.UD_FirstName ,' ')+' '+isnull(UserDetails.UD_MiddleName ,'')+' '+isnull(UserDetails.UD_LastName ,'') as MovedTo, convert(varchar,IssueForOthers.FromDate,103) as FromDate," +
                    " convert(varchar,IssueForOthers.ToDate,103) as ToDate, Direction.DirectionName FROM         UserDetails INNER JOIN " +
                    " IssueForOthers INNER JOIN  Direction ON IssueForOthers.Direction = Direction.DirectionId ON UserDetails.UD_UserPersonalID = IssueForOthers.MovedTo INNER JOIN " +
                    " UserDetails AS UserDetails_1 ON IssueForOthers.MovedBy = UserDetails_1.UD_UserPersonalID ";

            DataSet procureDS = commonClass.ExecuteQuery(query);
            return procureDS;
        }
        public DataSet GetIssueForOthers(int id)
        {
             EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "SELECT     ItemName, Quantity, Specification, Id FROM         IssueOthers  where Id='"+id+"'";
            DataSet procureDS = commonClass.ExecuteQuery(query);
            return procureDS;
        }

        public DataSet GetProductListByProductID(int productID)
        {
            EB.Generics.Generics commonClass = new EB.Generics.Generics();
            string query = string.Empty;
            query = "SELECT     ProductList.ProductCode, ProductList.ProductName, ProductType.TypeName, ProductList.ListPrice, ProductList.Stock, ProductStock.Quantity " +
                    " FROM ProductList INNER JOIN  ProductType ON ProductList.Type = ProductType.Type INNER JOIN " +
                    " ProductStock ON ProductList.ProductID = ProductStock.ProductID where  ProductList.ProductID='"+productID+"'";
            DataSet procureDS = commonClass.ExecuteQuery(query);
            return procureDS;
        }
        }
        #endregion
    }

    
